package com.sf.iisp.bind.dao.impl;


import java.math.BigInteger;
import java.util.ArrayList;
import java.util.List;

import org.hibernate.SQLQuery;
import org.hibernate.criterion.DetachedCriteria;
import org.hibernate.criterion.Restrictions;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;

import com.sf.iisp.bind.dao.IBindingDao;
import com.sf.iisp.bind.domain.BindingRecord;
import com.sf.iisp.bind.helper.DetachedCriteriaGenerator;
import com.sf.novatar.base.dao.BaseBeanDao;
import com.sf.novatar.base.dao.IPage;
import com.sf.novatar.base.domain.RequestParams;

import org.apache.commons.lang3.StringUtils;

@Repository
public class BindingDao extends BaseBeanDao<Long, BindingRecord> implements IBindingDao {

    @Override
    public BindingRecord getByVirtualNoAndPhone(String virtualNo, String courierPhone, String customerPhone) {

        String stateFlag = "1";//已绑定标志

        String sql = "select * from bind_record br where "
                + "(br.virtual_no =:virtualNo and br.state =:state and br.customer_phone =:customerPhone)"
                + " or (br.virtual_no =:virtualNo and br.state =:state and br.courier_phone =:courierPhone);";
        SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
        query.setString("state", stateFlag);
        query.setString("virtualNo", virtualNo);
        query.setString("customerPhone", customerPhone);
        query.setString("courierPhone", courierPhone);
        query.addEntity(BindingRecord.class);
        @SuppressWarnings("unchecked")
        ArrayList<BindingRecord> list = (ArrayList<BindingRecord>) query.list();
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public BindingRecord findBindRecordByPhone(String stateFlag, String courierPhone, String customerPhone) {
        String sql = "select  *  from bind_record  b where  b.state=:state and b.courier_phone =:courierPhone  and b.customer_phone =:customerPhone";
        SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
        query.setString("state", stateFlag);
        query.setString("customerPhone", customerPhone);
        query.setString("courierPhone", courierPhone);
//        query.setString("virtualNo", virtualNo);
        query.addEntity(BindingRecord.class);
        @SuppressWarnings("unchecked")
        ArrayList<BindingRecord> list = (ArrayList<BindingRecord>) query.list();
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }

    @Override
    @Transactional(rollbackFor = Exception.class)
    public BindingRecord findBindRerordByTransId(String transId) {
        String sql = "select  *  from bind_record  b where  b.trans_id=:transId";
        SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
        query.setString("transId", transId);
//        query.setString("virtualNo", virtualNo);
        query.addEntity(BindingRecord.class);
        @SuppressWarnings("unchecked")
        ArrayList<BindingRecord> list = (ArrayList<BindingRecord>) query.list();
        if (list != null && list.size() > 0) {
            return list.get(0);
        }
        return null;
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void saveBindingRecord(BindingRecord bindingRecord) {
        save(bindingRecord);
    }

    @Override
    public int getBindCount(String state) {
        String sql = "select count(DISTINCT t.virtualno ) from bind_record t where  t.state =:state ";
        SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
        query.setString("state", state);
        BigInteger uniqueResult = (BigInteger) query.uniqueResult();
        return uniqueResult.intValue();
    }


    @Override
    public List<BindingRecord> getBindRecord(String state) {
        String sql = "select * from bind_record t where  t.state =:state ";
        SQLQuery query = this.getCurrentSession().createSQLQuery(sql);
        query.setString("state", state);
        List bindList = query.list();
        return bindList;
    }


    @Override
    @Transactional(rollbackFor = Exception.class)
    public void updateSaveBindRecord(BindingRecord bindingRecord) {
        saveOrUpdate(bindingRecord);
    }


    @Override
    public IPage<BindingRecord> findPageBindingRecord(RequestParams queryParam) {
        /*Session session=super.getCurrentSession();
        StringBuffer sb=new StringBuffer("select k.* from bind_record k where 1=1 ");
		Set<String> existParamNames=new HashSet<String>();
		//拼sql和记录不为空的条件字段名称到集合中
		generateSqlAndExistParamNames(requestParams, sb, existParamNames);
		//设置parameters
        SQLQuery sqlQuery = setParamsToSqlQuery(requestParams, session, existParamNames, sb);       
        //分页
        sqlQuery.setFirstResult(requestParams.getPageSize()*(requestParams.getPageIndex()));
        sqlQuery.setMaxResults(requestParams.getPageSize());
       // sqlQuery.setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        //List<Map<String,Object>> mapList = sqlQuery.list();
        //List<BindAll> resultList = new ArrayList<BindAll>();
        if(CollectionUtils.isNotEmpty(mapList))
        {
	        for (int i = 0;i<mapList.size();i++) {
	        	BindAll ar = new BindAll();
	        	ar.setId(Long.parseLong((mapList.get(i).get("id")).toString()));
	        	ar.setVirtualNo((String) mapList.get(i).get("virtualno"));
	        	ar.setState((String) mapList.get(i).get("state"));
	        	ar.setTransTimestamp((String) mapList.get(i).get("transtimestamp"));
	        	ar.setMsgType((String)(mapList.get(i).get("msgtype")));
	        	ar.setSysCode((String)(mapList.get(i).get("syscode")));
	        	ar.setCourierPhone((String)(mapList.get(i).get("courierphone")));
	        	ar.setCustomerPhone((String)(mapList.get(i).get("customerphone")));
	        	ar.setBindTime((Date)mapList.get(i).get("bindtime"));
	        	ar.setReservedTime((String)mapList.get(i).get("reservedtime"));
	        	ar.setExtendField1((String)(mapList.get(i).get("extendfield1")));
	        	ar.setExtendField2((String)(mapList.get(i).get("extendfield2")));
	        	ar.setInvalidTime((String) mapList.get(i).get("invalidtime"));
	        	resultList.add(ar);
			}
        }
        sqlQuery.addEntity(BindingRecord.class);
        List<BindingRecord> resultList = (List<BindingRecord>)sqlQuery.list();
		return new Page<BindingRecord>(resultList, 0, requestParams.getPageSize(), requestParams.getPageIndex());*/

        DetachedCriteria criteria = DetachedCriteriaGenerator.generateCriteria(queryParam, super.entityClass);
        queryParam.setSortField("bindTime");
        queryParam.setAsc(false);

        try {
            String queryValue = null;
            queryValue = queryParam.getQueryValue("courierPhone");

            if (StringUtils.isNotEmpty(queryValue)) {
                criteria.add(Restrictions.eq("courierPhone", queryValue.trim()));
            }

            queryValue = queryParam.getQueryValue("customerPhone");
            if (StringUtils.isNotEmpty(queryValue)) {
                criteria.add(Restrictions.eq("customerPhone", queryValue.trim()));
            }

            queryValue = queryParam.getQueryValue("virtualNo");
            if (StringUtils.isNotEmpty(queryValue)) {
                criteria.add(Restrictions.eq("virtualNo", queryValue.trim()));
            }

        } catch (Exception e) {
            throw new IllegalArgumentException(e);
        }

        return super.findPageBy(criteria, queryParam.getPageSize(),
                queryParam.getPageIndex(), queryParam.getSortField(),
                queryParam.isAsc());
    }


    @Override
    public BindingRecord loadById(String id) {
        return load(Long.valueOf(id));
    }
	
	/*private SQLQuery setParamsToSqlQuery(RequestParams requestParams, Session session, Set<String> existParamNames,
			StringBuffer sqlSb) {
		SQLQuery sqlQuery=session.createSQLQuery(sqlSb.toString());
        for(String s : existParamNames){
        	sqlQuery.setParameter(s, requestParams.getQueryValue(s));
        }
		return sqlQuery;
	}

	private void generateSqlAndExistParamNames(RequestParams requestParams, StringBuffer sb, Set<String> existParamNames) {
		String queryValue = null;
		queryValue = requestParams.getQueryValue("extendfield1");
        if(StringUtils.isNotEmpty(queryValue)){
        	sb.append(" and k.extendfield1=:extendfield1");
        	existParamNames.add("extendfield1");
        }
        sb.append(" order by k.bindtime desc");
	}*/

}
